package com.ecsolutions.service;

import com.ecsolutions.dao.Utilization_Dao;
import com.ecsolutions.entity.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * Created by Administrator on 2017/7/31.
 */
@Service("Utilization_Service")
public class Utilization_ServiceImpl implements Utilization_Service {

    private Utilization_Dao utilization_dao;

    @Autowired
    public Utilization_ServiceImpl(Utilization_Dao utilization_dao) {
        this.utilization_dao = utilization_dao;
    }

    @Override
    public Utilization_Entity getPopupInfo() {
        Utilization_Entity utilization_entity = new Utilization_Entity();
        List<HashMap<String, String>> linenoList = utilization_dao.getLinenoList();
        List<HashMap<String, String>> biaoshiList = utilization_dao.getFlagList();
        List<HashMap<String, String>> ccyList = utilization_dao.getCcyList();
        List<HashMap<String, String>> fanganList = utilization_dao.getFanganList();
        List<HashMap<String, String>> meileiList = utilization_dao.getMengleiList();

        utilization_entity.setLinenoList(linenoList);
        utilization_entity.setBiaoshiList(biaoshiList);
        utilization_entity.setCcyList(ccyList);
        utilization_entity.setFanganList(fanganList);
        utilization_entity.setMeileiList(meileiList);

        return utilization_entity;
    }

    @Override
    public List<Utilization_Entity> getByCustcodInfo(String custcod) {
        return utilization_dao.getByCustcodInfo(custcod);
    }

    @Override
    public List<HashMap<String, String>> getByMenleiInfo(String dircflag1) {
        return utilization_dao.getDaleiList(dircflag1);
    }

    @Override
    public List<HashMap<String, String>> getByMenleiAndDaleiInfo(String dircflag1, String dircflag2) {
        return utilization_dao.getZhongleiList(dircflag1,dircflag2);
    }

    @Override
    public List<HashMap<String, String>> getByMenleiAndDaleiAndZhongleiInfo(String dircflag1, String dircflag2, String dircflag3) {
        return utilization_dao.getXiaoleiList(dircflag1,dircflag2,dircflag3);
    }

    @Override
    public Facility_Entity getFacilityInfoByCustcodAndLineno(String custcod, String lineno) {
        return utilization_dao.getByCustcodAndLinenoInfo(custcod,lineno);
    }

    @Override
    public Utilization_Entity getUtilizationinfo(String bpm_no) {
        return utilization_dao.getUtilizationInfoByBpm_no(bpm_no);
    }

    @Override
    public Facility_Entity getFacilityInfo(String bpm_no) {
        return utilization_dao.getFacilityInfoByBpm_no(bpm_no);
    }

    @Override
    public List<HashMap<String, Object>> getByLiLvInfo(String drawccy) {
        return utilization_dao.getLilvList(drawccy);
    }

    @Override
    public String getNianlilvInfo(String drawccy, String ratetype) {
        return utilization_dao.getNianLilvInfo(drawccy,ratetype);
    }

    @Override
    public String getJingLiMingChenInfo(String accoffic) {
        return utilization_dao.getJingLiInfo(accoffic);
    }

    @Override
    public Utilization_Entity getUtilizationByCustcodAndLineno(String custcod, String lineno,String loanref) {
        return utilization_dao.getUtilizationByCustcodAndLineno(custcod,lineno,loanref);
    }

    @Override
    public void saveUtilizationInfo(Utilization_Entity utilization_entity) {
        utilization_dao.saveUtilizationInfo(utilization_entity);
    }

    @Override
    public List<HashMap<String, Object>> getRatetypeList(String drawccy) {
        return utilization_dao.getLilvList(drawccy);
    }

    @Override
    public List<HashMap<String, String>> getDaleiList(String dorcflag1) {
        return utilization_dao.getDaleiList(dorcflag1);
    }

    @Override
    public List<HashMap<String, String>> getZhongleiList(String dorcflag1, String dorcflag2) {
        return utilization_dao.getZhongleiList(dorcflag1,dorcflag2);
    }

    @Override
    public List<HashMap<String, String>> getXiaoleiList(String dorcflag1, String dorcflag2, String dorcflag3) {
        return utilization_dao.getXiaoleiList(dorcflag1,dorcflag2,dorcflag3);
    }

    @Override
    public void updateUtilizationInfo(Utilization_Entity utilization_entity) {
        utilization_dao.updateUtiltzationInfo(utilization_entity);
    }

    @Override
    public void deleteUtilizationInfo(String custcod, String lineno, String loanref) {
        utilization_dao.deleteUtilizationInfo(custcod,lineno,loanref);
    }

    @Override
    public ApplicationInfo_Entity getByLoanrefInfo(String loanref) {
        return utilization_dao.getByLoanrefInfo(loanref);
    }

    @Override
    public void saveApplicationInfo(ApplicationInfo_Entity applicationInfo_entity) {
        utilization_dao.saveApplicationInfo(applicationInfo_entity);
    }

    @Override
    public void updateApplicationInfo(ApplicationInfo_Entity applicationInfo_entity) {
        utilization_dao.updateApplicationInfo(applicationInfo_entity);
    }

    @Override
    public Loanpaymentinfo_Entity getLoanpaymentinfo(String loanref) {
        return utilization_dao.getLoanPaymentInfo(loanref);
    }

    @Override
    public Loanpaymentinfo_det_Entity getLoanpaymentdetinfo(String loanref) {
        return utilization_dao.getLoanPayment_detInfo(loanref);
    }

    @Override
    public String getFenZhiHang(String custcode) {
        return utilization_dao.getBankcode(custcode);
    }

    @Override
    public String getLastName(String custcode) {
        return utilization_dao.getLastname(custcode);
    }

    @Override
    public HashMap getApplicationInfo(String custcode) {
        return utilization_dao.getLoanApplicant(custcode);
    }

    @Override
    public Integer getMaxId() {
        return utilization_dao.getMaxId();
    }

    @Override
    public List<Loanpaymentinfo_det_Entity> getLoanpaymentinfoList(String custcode, String loanref) {
        return utilization_dao.getLoanApplicantInfoList(custcode,loanref);
    }

    @Override
    public void saveLoanpaymentInfo(Loanpaymentinfo_Entity loanpaymentinfo_entity) {
        utilization_dao.saveLoanpaymentInfo(loanpaymentinfo_entity);
    }

    @Override
    public void updateLoanpaymentInfo(Loanpaymentinfo_Entity loanpaymentinfo_entity) {
        utilization_dao.updateLoanpaymentInfo(loanpaymentinfo_entity);
    }

    @Override
    public void saveLoanpaymentInfodet(Loanpaymentinfo_det_Entity loanpaymentinfo_det_entity) {
        utilization_dao.saveLoanpaymentInfodet(loanpaymentinfo_det_entity);
    }

    @Override
    public void updateLoanpaymentInfodet(Loanpaymentinfo_det_Entity loanpaymentinfo_det_entity) {
        utilization_dao.updateLoanpaymentInfodet(loanpaymentinfo_det_entity);
    }

    @Override
    public void deleteLoanpaymentInfo(Integer id) {
        utilization_dao.deleteLoanpaymentInfodet(id);
    }

    @Override
    public List<Pay_Entity> getPayList(String custcod, String loanref) {
        return utilization_dao.getPayList(custcod,loanref);
    }

    @Override
    public Boolean updateFacilityInfo(Integer osamt, Integer avliamt, String linestatus, String custcod, String lineno) {
        return utilization_dao.updateFacilityinfo(osamt,avliamt,linestatus,custcod,lineno);
    }

    @Override
    public Boolean updateFacilityInfoTwo(Integer avliamt, Integer holdamt, String linestatus, String custcod, String lineno) {
        return utilization_dao.updateFacilityinfoTwo(avliamt,holdamt,linestatus,custcod,lineno);
    }

    @Override
    public Date getDuedateByUtilizationInfo(Utilization_Entity utilization_entity) {
        Date firstpaydate = utilization_entity.getFirstpaydate();//首次还款日
        Date drawdate = utilization_entity.getDrawdate();//放款日
        String tenor = utilization_entity.getTenor();//期数
        String payfreq = utilization_entity.getPayfreq();//周期
        String payper = utilization_entity.getPayper();//年月日Y/M/D
        if(firstpaydate!=null && drawdate!=null && !tenor.equals("") && !payfreq.equals("") && !payper.equals("")){
            Calendar c = Calendar.getInstance();
            c.setTime(utilization_entity.getFirstpaydate());
            if(payper.equals("Y")){
                c.add(Calendar.YEAR,(Integer.parseInt(tenor)-1)*Integer.parseInt(payfreq));
            }
            if(payper.equals("M")){
                c.add(Calendar.MONTH,(Integer.parseInt(tenor)-1)*Integer.parseInt(payfreq));
            }
            if(payper.equals("D")){
                c.add(Calendar.DATE,(Integer.parseInt(tenor)-1)*Integer.parseInt(payfreq));
            }
            Date duedate = c.getTime();
            return duedate;
        }
        return null;
    }

    @Override
    public String derivarExcel(List<Pay_Entity> payList, Utilization_Entity utilization_entity) {
        //总还利息、总还款额、总还本金
        ExcelDownld_Entity SumInfo = utilization_dao.getSumInfo(utilization_entity.getLoanref());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        HSSFWorkbook wb = new HSSFWorkbook();//1.创建一个workbook，对应一个Excel文件
        HSSFSheet sheet = wb.createSheet("还款计划表");// 2.在workbook中添加一个sheet，对应Excel中的一个sheet
        //设置样式1
        CellStyle styleOne = wb.createCellStyle(); //创建样式对象1
        styleOne.setAlignment(HorizontalAlignment.CENTER);//水平居中
        styleOne.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置样式2
        CellStyle styleTwo = wb.createCellStyle(); //创建样式对象2
        //设置边框粗细
        styleTwo.setBorderTop(BorderStyle.THIN);//上边框细
        styleTwo.setBorderBottom(BorderStyle.THIN);//下边框细
        styleTwo.setBorderLeft(BorderStyle.THIN);//左边框细
        styleTwo.setBorderRight(BorderStyle.THIN);//右边框细
        //设置边框颜色黑色
        styleTwo.setTopBorderColor(IndexedColors.BLACK.index);//上边框黑色
        styleTwo.setBottomBorderColor(IndexedColors.BLACK.index);//下边框黑色
        styleTwo.setLeftBorderColor(IndexedColors.BLACK.index);//左边框黑色
        styleTwo.setRightBorderColor(IndexedColors.BLACK.index);//右边框黑色
        //创建单元格在第二行
        Row row2 = sheet.createRow((short) 2);
        sheet.addMergedRegion(new CellRangeAddress(  2,2, 0, 6));// 设置单元格合并
        row2.createCell(0).setCellValue("还款计划表草案");
        row2.getCell(0).setCellStyle(styleOne);//设置样式1
        //创建单元格在第四行
        Row row4 = sheet.createRow((short) 4);
        row4.createCell(0).setCellValue("贷款总额："+utilization_entity.getDrawamt());
        //创建单元格在第五行
        Row row5 = sheet.createRow((short) 5);
        row5.createCell(0).setCellValue("货币："+utilization_entity.getDrawccy());
        //创建单元格在第六行
        Row row6 = sheet.createRow((short) 6);
        sheet.addMergedRegion(new CellRangeAddress(6,6,0,1));//合并第六行单元格第1和2两格
        row6.createCell(0).setCellValue("贷款拨付/发放日期");
        row6.getCell(0).setCellStyle(styleOne);

        row6.createCell(2).setCellValue("贷款总额");
        row6.getCell(2).setCellStyle(styleOne);
        row6.getCell(2).setCellStyle(styleTwo);

        row6.createCell(4).setCellValue("年利率（%）");
        row6.getCell(4).setCellStyle(styleOne);
        row6.getCell(4).setCellStyle(styleTwo);

        row6.createCell(6).setCellValue("起息日期");
        row6.getCell(6).setCellStyle(styleOne);
        row6.getCell(6).setCellStyle(styleTwo);

        Row row7 = sheet.createRow((short) 7);
        sheet.addMergedRegion(new CellRangeAddress(  7,7, 0, 1));// 设置单元格合并
        row7.createCell(0).setCellValue(sdf.format(utilization_entity.getDrawdate()));
        row7.getCell(0).setCellStyle(styleTwo);

        row7.createCell(2).setCellValue(utilization_entity.getDrawamt());
        row7.getCell(2).setCellStyle(styleTwo);

        row7.createCell(4).setCellValue(utilization_entity.getSchemarate());
        row7.getCell(4).setCellStyle(styleTwo);

        row7.createCell(6).setCellValue(sdf.format(utilization_entity.getDrawdate()));
        row7.getCell(6).setCellStyle(styleTwo);

        Row row9 = sheet.createRow((short) 9);
        row9.createCell(0).setCellValue("期数");
        row9.createCell(1).setCellValue("日期");
        row9.createCell(2).setCellValue("计息天数");
        row9.createCell(3).setCellValue("还款金额");
        row9.createCell(4).setCellValue("本金");
        row9.createCell(5).setCellValue("利息");
        row9.createCell(6).setCellValue("余额");
        row9.getCell(0).setCellStyle(styleTwo);
        row9.getCell(1).setCellStyle(styleTwo);
        row9.getCell(2).setCellStyle(styleTwo);
        row9.getCell(3).setCellStyle(styleTwo);
        row9.getCell(4).setCellStyle(styleTwo);
        row9.getCell(5).setCellStyle(styleTwo);
        row9.getCell(6).setCellStyle(styleTwo);

        for(int i=0;i<payList.size();i++){
            Row row9plus = sheet.createRow((short) (10 + i));
            row9plus.createCell(0).setCellValue(payList.get(i).getInstno());
            row9plus.createCell(1).setCellValue(sdf.format(payList.get(i).getEnddate()));
            if(i==0){
                row9plus.createCell(2).setCellValue((payList.get(i).getEnddate().getTime()-utilization_entity.getDrawdate().getTime())/86400000);
            }else{
                row9plus.createCell(2).setCellValue((payList.get(i).getEnddate().getTime()-payList.get(i-1).getEnddate().getTime())/86400000);
            }
            row9plus.createCell(3).setCellValue(payList.get(i).getInstamt().toString());
            row9plus.createCell(4).setCellValue(payList.get(i).getPrnamt().toString());
            row9plus.createCell(5).setCellValue(payList.get(i).getIntamt().toString());
            row9plus.createCell(6).setCellValue(payList.get(i).getOsamt().toString());

            for (int j = 0; j < 7; j++) {
                row9plus.getCell(j).setCellStyle(styleTwo);
            }
        }

        Row rowsum = sheet.createRow((short)(10+payList.size()));
        rowsum.createCell(0).setCellValue("合计："+payList.size()+"期");
        rowsum.getCell(0).setCellStyle(styleTwo);
        rowsum.createCell(3).setCellValue(SumInfo.getSum_instamt());
        rowsum.getCell(3).setCellStyle(styleTwo);
        rowsum.createCell(4).setCellValue(SumInfo.getSum_prnamt());
        rowsum.getCell(4).setCellStyle(styleTwo);
        rowsum.createCell(5).setCellValue(SumInfo.getSum_intamt());
        rowsum.getCell(5).setCellStyle(styleTwo);
        rowsum.createCell(6).setCellValue("");
        rowsum.getCell(6).setCellStyle(styleTwo);

        //文件保存在桌面
        try {
            FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\还款计划表.xls");
            // 把上面创建的工作簿输出到文件中
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            return "文件已下载到桌面";
        }catch(IOException e){
            e.printStackTrace();
            return "文件下载失败";
        }
    }

    @Override
    public void export(HSSFWorkbook wb, HttpServletResponse response, HttpServletRequest request){

        try {
            String filename = "还款计划表"+".xls";
            String userAgent = request.getHeader("User-Agent");
            if(userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                filename = URLEncoder.encode(filename, "UTF-8");
            }else {
                filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
            }
            response.addHeader("Content-Disposition", "attachment;filename=" + filename);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            OutputStream out = response.getOutputStream();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wb.write(baos);
            byte [] xlsBytes = baos.toByteArray();
            out.write(xlsBytes);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
